﻿using Business.Dto;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using WoChu.Infrastructure.Data;
using WoChu.Infrastructure.ModelCommmon;
using WoChu.WMS.Repository;
using WoChu.Infrastructure.Collection;

namespace Business.Service
{
    public class LocationReportService
    {
        LocationReportRepository locationReportRep = new LocationReportRepository();
        public void GetPageList(PageInfo<dynamic, LocationReportInput> pageInfo)
        {
            string select = "", tableName = "", where = "1=1", orderby = "";
            LocationReportType type = pageInfo.Condition.ReportTypeEnum;
            switch (type)
            {
                case LocationReportType.待进货单查询报表:
                    select = @"
                P.SHEETID,V.VENDERCODE,V.VENDERNAME,P.EXPIREDATE,P.PURTYPE,P.TOTALQTY,P.TOTALCOSTVALUE,P.FLAG,
                PI.GOODSID,PI.QTY,PI.ORDERDATE1,PI.QTY1,PI.ORDERDATE2,PI.QTY2,PI.ORDERDATE3,PI.QTY3,PI.REALQTY,PI.REMARK,
                G.GOODSCODE,G.GOODSNAME,G.UNITNAME,G.GOODSSPEC,G.CELLNO,CE.LOOKUP CELLNAME,LG.DIRECTSELLINGGOODSID
                ";
                    tableName = @"
                    PURCHASE P
                    JOIN PURCHASEITEM PI ON PI.SHEETID=P.SHEETID 
                    JOIN GOODS G ON G.GOODSID=PI.GOODSID
                    JOIN VENDER V ON V.VENDERID=P.VENDERID
                    LEFT JOIN LMLOCATIONGOODS LG ON LG.GOODSID=PI.GOODSID
                    LEFT JOIN LOOKUPITEM CE ON CE.LOOKUPID='CELLNO' AND G.CELLNO=CE.FIELDVALUE
                    ";
                    where = @"";
                    orderby = "P.SHEETID";
                    break;
                #region 上架商品信息查询
                case LocationReportType.上架商品信息查询:
                    select = @"
        W.WAREHOUSENAME,T4.CELLNO,T1.FLAG,T1.CREATOR,T1.CREATEDATE,T1.OBJECTTYPE,T1.EDITOR,T1.REMARK,T2.GOODSBATCHCODE,T2.FROMLOCATIONID
       ,T2.TOLOCATIONID,T2.GOODSID,T2.MAXQTY MAXQTY,T2.QTY,T2.OFFSHELVESID,T3.GOODSCODE,T3.GOODSNAME,T3.GOODSSPEC,T3.PURUNITNAME,t3.UNITNAME,T3.GOODSCLASS1,T3.GOODSCLASS2,T3.CLASSCODE
       ,SUBSTR(TO_CHAR(SYSDATE,'YYYY')，0,3)|| SUBSTR(T2.GOODSBATCHCODE,LENGTH(GOODSBATCHCODE)-7,1)||'/'||SUBSTR(T2.GOODSBATCHCODE,LENGTH(GOODSBATCHCODE)-6,2)||'/'||SUBSTR(T2.GOODSBATCHCODE,LENGTH(GOODSBATCHCODE)-4,2)
       AS BEGINDATE,TO_NUMBER(SUBSTR(T2.GOODSBATCHCODE,LENGTH(T2.GOODSBATCHCODE)-2,3)) AS FLASHDAYS,T4.LOCATIONCODE,
       CASE WHEN SUBSTR(T3.GOODSCODE,0,3)='WOC' THEN T3.PRICE ELSE T3.COST END AS PRICECOST,
       NVL(CASE WHEN SUBSTR(T3.GOODSCODE,0,3)='WOC' THEN T3.PRICE ELSE T3.COST END,0)*T2.MAXQTY AS AMOUNT
                    ";
                    tableName = @"Lmgoodsmove t1,Lmgoodsmoveitem t2,GOODS t3,LMLocation t4,warehouse W";
                    where = @"
                t1.goodsmoveid=t2.goodsmoveid and t2.goodsid=t3.goodsid and t2.Tolocationid=t4.locationID and t1.Warehouseid=W.Warehouseid 
                and t1.Movetype='ON'";
                    orderby = "t1.createdate desc";
                    break;
                #endregion
                #region 下架商品信息查询
                case LocationReportType.下架商品信息查询:
                    select = @"
        W.WAREHOUSENAME,t4.Cellno,t1.Flag,t1.Creator,t1.createdate,t5.batchno,t1.objecttype
       ,t1.Editor,t1.remark,t2.Goodsbatchcode,t2.Fromlocationid,t4.locationcode,t3.goodsclass2,t3.classcode
       ,t2.Tolocationid,t2.Goodsid,t2.Qty,t2.OffShelvesID,t3.goodscode,t3.goodsname,t3.goodsspec,t3.unitname
       ,substr(to_char(sysdate,'yyyy')，0,3)|| substr(t2.goodsbatchcode,length(goodsbatchcode)-7,1)||'/'||substr(t2.goodsbatchcode,length(goodsbatchcode)-6,2)||'/'||substr(t2.goodsbatchcode,length(goodsbatchcode)-4,2) as BBegindate
       ,to_number(substr(t2.goodsbatchcode,length(t2.goodsbatchcode)-2,3)) as FlashDays,offshelvesType,
       case when substr(t3.Goodscode,0,3)='WOC' then trunc(t3.Price/t3.purunitqty,2) else trunc(t3.Cost/t3.purunitqty,2) end as PriceCost,
       trunc(nvl(case when substr(t3.Goodscode,0,3)='WOC' then t3.Price/t3.purunitqty else t3.Cost/t3.purunitqty end,0)*t2.qty,2) as Amount
                    ";
                    tableName = @"
            Lmgoodsmove t1 
            join (Select goodsmoveid, seqno, goodsbatchcode, fromlocationid, tolocationid, goodsid, qty, offshelvesid, odds, maxqty,
            (case offshelvesid when 0 then '盲扫下架' else '指令下架' end) as offshelvesType from Lmgoodsmoveitem) t2 on t1.goodsmoveid=t2.goodsmoveid
            join GOODS t3 on t2.goodsid=t3.goodsid
            join LMLocation t4 on t2.Fromlocationid=t4.locationID
            left join lmoffshelves t5 on t2.offshelvesid=t5.offshelvesid
            join warehouse W on t1.Warehouseid=W.Warehouseid
                    ";
                    where = @"t1.Movetype='OFF'";
                    orderby = "t1.createdate desc";
                    break;
                #endregion
                #region 货位批次实时库存查询
                case LocationReportType.货位批次实时库存查询:
                    select = @"
        V.CELLNO,V.LOCATIONCODE,V.GOODSBATCHCODE,V.Goodscode,V.Goodsname,V.Goodsspec,V.QTY,V.Unitname,
       V.ODDS,V.PurQty,V.Purunitname,V.ProductionDate,V.ExpiryDays,G.Classcode,G.Goodsclass1,G.Goodsclass2,G.KeepDays,
       V.ExpiryDays-V.diffDays as OverDays,  --临保天数
       trunc((V.ExpiryDays-V.diffDays)/ExpiryDays,2) as overdaysss,
       case when (ExpiryDays>365 and V.ExpiryDays-V.diffDays>ExpiryDays/6+10 ) then '' 
            when (ExpiryDays>365 and V.ExpiryDays-V.diffDays>=ExpiryDays/6 ) then '预警'  
            when (ExpiryDays>365 and V.ExpiryDays-V.diffDays<ExpiryDays/6) then '报损' 
            when (ExpiryDays>180 and V.ExpiryDays-V.diffDays>ExpiryDays/5+10 ) then '' 
            when (ExpiryDays>180 and V.ExpiryDays-V.diffDays>=ExpiryDays/5 ) then '预警'  
            when (ExpiryDays>180 and V.ExpiryDays-V.diffDays<ExpiryDays/5) then '报损'
            when (ExpiryDays>30 and V.ExpiryDays-V.diffDays>ExpiryDays/4+10 ) then '' 
            when (ExpiryDays>30 and V.ExpiryDays-V.diffDays>=ExpiryDays/4 ) then '预警'  
            when (ExpiryDays>30 and V.ExpiryDays-V.diffDays<ExpiryDays/4) then '报损'
            else '30以内' end as earlywarning,  --预警
         (SELECT CASE WHEN (V.DiffDays>V.ExpiryDays*T3.TIMEPROPORTION+T3.PLUSDAYS) then '报损' else '' end  
          FROM GOODS T2 
          JOIN Lmwarrantytime T3 ON T3.RULECLASS=T2.RULECLASS AND T3.STATUS=1
          WHERE V.GOODSID=T2.GOODSID and V.ExpiryDays BETWEEN T3.BEGININT AND T3.ENDINT
          ) eggswarning ";
                    tableName = @"v_lminv V
                    join goods G on V.GOODSID=G.Goodsid
                    ";
                    where = @"V.qty<>0";
                    orderby = "V.CELLNO";
                    break;
                #endregion
                #region 货位总量实时库存查询
                case LocationReportType.货位总量实时库存查询:
                    select = @"
       T.*,'' VENDERCODE,'' VENDERNAME,nvl(OS.Sumqty,0) AS OnSumqty,round(nvl(OOS.Sumqty,0)/nvl(G.PurUnitQty,1),2) as OffSumqty,
       round(nvl(OOS.Sumqty,0)/nvl(G.PurUnitQty,1)/to_number(to_date(to_char(sysdate,'yyyy-MM'),'yyyy-fmmm')-to_date(to_char(add_months(sysdate,-1),'yyyy-MM'),'yyyy-fmmm')),2) as Offavg,
       ----case when substr(G.Goodscode,0,3)='WOC' then G.Price else round(G.Cost/nvl(G.PurUnitQty,1),3) end PriceCost,   --采购单位进价/售价售价
       case when substr(G.Goodscode,0,3)='WOC' then G.Price else round(G.Cost,3) end PriceCost,
       case when substr(G.Goodscode,0,3)='WOC' then G.Price*T.Qty else round(G.Cost*T.Qty/nvl(G.PurUnitQty,1),3) end as Amount,   -----总金额
       case when T.PurQty<= round(nvl(OOS.Sumqty,0)*3/nvl(G.PurUnitQty,1)/to_number(to_date(to_char(sysdate,'yyyy-MM'),'yyyy-fmmm')-to_date(to_char(add_months(sysdate,-1),'yyyy-MM'),'yyyy-fmmm')),2) then '库存预警' else '正常' end as InvWarn
       ,G.goodsclass2,G.classcode,G.KeepDays";
                    tableName = @"
        (select V.CELLNO,V.LOCATIONCODE,V.Goodscode,V.Goodsname,V.Goodsspec,V.GOODSID,V.Unitname,V.ODDS,V.Purunitname,V.Venderid,
           Sum(V.QTY) as Qty,Sum(V.PurQty) as PurQty
            from v_lminv V  
            --where V.qty<>0
            group by V.CELLNO,V.LOCATIONCODE,V.Goodscode,V.Goodsname,V.Goodsspec,V.Unitname,V.GOODSID, V.ODDS,V.Purunitname,V.Venderid
            order by V.Goodscode 
           ) T
           left join goods G on G.Goodsid=T.Goodsid 
          -- Left join Vender VD on G.VenderID=VD.VenderID
           LEFT JOIN (
            select a.goodsid,sum(a.qty) as Sumqty 
            from lmgoodsmoveitem a
            join Lmgoodsmove b on a.goodsmoveid=b.goodsmoveid
            where to_char(b.movedate,'yyyy-MM')=to_char(add_months(sysdate,-1),'yyyy-MM')
            and b.movetype='ON'
            group by a.goodsid
        ) OS on OS.GOODSID=T.GOODSID
           LEFT JOIN (
            select a.goodsid,sum(a.qty) as Sumqty 
            from lmgoodsmoveitem a
            join Lmgoodsmove b on a.goodsmoveid=b.goodsmoveid
            where to_char(b.movedate,'yyyy-MM')=to_char(add_months(sysdate,-1),'yyyy-MM')
            and b.movetype='OFF'
            group by a.goodsid
        ) OOS on OOS.GOODSID=T.GOODSID 
                ";
                    where = @"T.Qty>=0";
                    orderby = "T.LocationCode";
                    break;
                #endregion
                case LocationReportType.货位库存日志流水查询:
                    select = @"LT.TRANDATE, W.WAREHOUSECODE, W.WAREHOUSENAME, L.LOCATIONCODE, L.CELLNO, 
                   LT.GOODSBATCHCODE, G.GOODSCODE, G.GOODSNAME, G.GOODSSPEC, G.UNITNAME, 
                   LT.SHEETTYPE, LT.SHEETID, LT.QTY, LT.BALANCE, LT.CREATOR, LT.CREATEDATE ";
                    tableName = @"LMLOCATIONINVENTORYTRAN LT
                    INNER JOIN GOODS G ON G.GOODSID=LT.GOODSID
                    LEFT JOIN LMLOCATION L ON LT.LOCATIONID=L.LOCATIONID
                    LEFT JOIN WAREHOUSE W ON W.WAREHOUSEID=L.WAREHOUSEID ";
                    where = @"";
                    orderby = "LT.TRANDATE";
                    break;
                case LocationReportType.下架指令查询:
                    select = @"OS.BATCHNO,G.GOODSCODE,G.GOODSNAME,LM.CELLNO,LM.LOCATIONCODE,OS.QTY,OS.QTYEXCE,OS.FLAG,
                    OS.OFFSHELVESDATE,OS.CREATER,OS.CREATEDATE,OS.EDITOR,OS.EDITDATE,OS.REMARK";
                    tableName = @"
            lmoffshelves os
            join goods g on os.goodsid=g.goodsid
            left join (select lml.cellno,lml.locationcode,lmlg.goodsid from  lmlocationgoods lmlg 
            join lmlocation lml on lmlg.locationid = lml.locationid and lml.warehouseid in (709,818) and lmlg.status=1
            )lm on g.goodsid = lm.goodsid";
                    where = @"os.qty>0";
                    orderby = "os.batchno";
                    break;
                case LocationReportType.货位库存调整报表:
                    select = @"
            C.Cellno,C.Locationcode,A.Goodsbatchcode,B.Goodsname,B.Goodsspec,B.Unitname,
           (A.Balance-A.Qty) as PreQty,A.Qty,A.Balance as BalaQty,A.Createdate, A.Creator";
                    tableName = @"
                lmlocationinventorytran A
                left join goods B on A.Goodsid=B.Goodsid
                left join Lmlocation C on C.Locationid=A.Locationid";
                    where = @"sheettype='AD'";
                    orderby = "C.Locationcode";
                    break;
                case LocationReportType.货位商品关联查询报表:
                    select = @" 
        L.LOCATIONCODE,W.WAREHOUSENAME,L.CELLNO,G.GOODSCODE,G.GOODSNAME,G.UNITNAME,LG.Creater,LG.Createdate,LG.Editor,LG.Editdate,
        CASE WHEN LG.STATUS=1 THEN '启用' ELSE '停用' END AS VAL";
                    tableName = @"LMLOCATIONGOODS LG
                JOIN LMLOCATION L ON LG.LOCATIONID=L.LOCATIONID
                JOIN WAREHOUSE W ON W.WAREHOUSEID=L.WAREHOUSEID
                JOIN GOODS G ON G.GOODSID=LG.GOODSID ";
                    where = @"";
                    orderby = "L.LOCATIONCODE";
                    break;
                #region 仓库商品日进销存报表
                case LocationReportType.仓库商品日进销存报表:
                    select = @"
        LID.INVENTORYDAYID,LID.CALCDATE,L.CELLNO,L.LOCATIONCODE,G.GOODSCODE GCODE,G.GOODSNAME,G.GOODSSPEC,LID.UNITNAME,LID.COST, LID.QTY, 
   LID.ONPURQTY,LID.ONSALEQTY,LID.ONWORKSHOPQTY,LID.ONOTHERQTY,LID.ADJUSTQTY,ONWOCHUQTY,ONQINGPUQTY,
   LID.OFFSALEQTY,LID.OFFUSEQTY,LID.OFFLOSSQTY,LID.OFFRETURNQTY,LID.OFFOTHERQTY,OFFQINGPUQTY,
   (LID.QTY+ LID.OFFSALEQTY+LID.OFFUSEQTY+LID.OFFLOSSQTY+LID.OFFRETURNQTY+LID.OFFOTHERQTY+OFFQINGPUQTY
   -LID.ONPURQTY-LID.ONSALEQTY-LID.ONWORKSHOPQTY-LID.ONOTHERQTY-LID.ADJUSTQTY-ONWOCHUQTY-ONQINGPUQTY) AS OpenQty 
        ";
                    tableName = @"
            LMINVENTORYDAYS LID
            JOIN GOODS G ON LID.GOODSID=G.GOODSID
            LEFT JOIN LMLOCATIONGOODS LG ON LG.GOODSID=LID.GOODSID AND LG.STATUS=1
            LEFT JOIN LMLOCATION L ON L.LOCATIONID=LG.LOCATIONID AND L.STATUS=1
            ";
                    where = @"";
                    orderby = "LID.CALCDATE";
                    break;
                #endregion
                #region 仓库商品汇总进销存报表
                case LocationReportType.仓库商品汇总进销存报表:
                    select = @"
    LID.INVENTORYDAYID,LID.CALCDATE,L.CELLNO,L.LOCATIONCODE,G.GOODSCODE gcode,G.GOODSNAME,G.GOODSSPEC,LID.UNITNAME,LID.COST, LID.QTY,
   SLID.ONPURQTY,SLID.ONSALEQTY,SLID.ONWORKSHOPQTY,SLID.ONOTHERQTY,SLID.ADJUSTQTY,SLID.ONWOCHUQTY,SLID.ONQINGPUQTY,
   SLID.OFFSALEQTY,SLID.OFFUSEQTY,SLID.OFFLOSSQTY,SLID.OFFRETURNQTY,SLID.OFFOTHERQTY,SLID.OFFQINGPUQTY,
   (LID.QTY+ SLID.OFFSALEQTY+SLID.OFFUSEQTY+SLID.OFFLOSSQTY+SLID.OFFRETURNQTY+SLID.OFFOTHERQTY+SLID.OFFQINGPUQTY
   -SLID.ONPURQTY-SLID.ONSALEQTY-SLID.ONWORKSHOPQTY-SLID.ONOTHERQTY-SLID.ADJUSTQTY-SLID.ONWOCHUQTY-SLID.ONQINGPUQTY) AS OpenQty
                ";
                    tableName = @"
        LMINVENTORYDAYS LID
    JOIN GOODS G ON LID.GOODSID=G.GOODSID --AND LID.CALCDATE={2}
    LEFT JOIN LMLOCATIONGOODS LG ON LG.GOODSID=LID.GOODSID AND LG.STATUS=1
    LEFT JOIN LMLOCATION L ON L.LOCATIONID=LG.LOCATIONID AND L.STATUS=1
    LEFT JOIN (SELECT LID1.GOODSID,
             SUM(LID1.ONPURQTY) ONPURQTY ,SUM(LID1.ONSALEQTY) ONSALEQTY,SUM(LID1.ONWORKSHOPQTY) ONWORKSHOPQTY,SUM(LID1.ONOTHERQTY) ONOTHERQTY,SUM(LID1.ADJUSTQTY) ADJUSTQTY,SUM(LID1.ONWOCHUQTY) ONWOCHUQTY,SUM(LID1.ONQINGPUQTY) ONQINGPUQTY,
             SUM(LID1.OFFSALEQTY) OFFSALEQTY,SUM(LID1.OFFUSEQTY) OFFUSEQTY,SUM(LID1.OFFLOSSQTY) OFFLOSSQTY,SUM(LID1.OFFRETURNQTY) OFFRETURNQTY,SUM(LID1.OFFOTHERQTY) OFFOTHERQTY,SUM(LID1.OFFQINGPUQTY) OFFQINGPUQTY
            FROM LMINVENTORYDAYS LID1
           -- WHERE LID1.CALCDATE<={2} {1}
            GROUP BY LID1.GOODSID
    ) SLID ON LID.GOODSID=SLID.GOODSID
                ";
                    where = @"";
                    orderby = "LID.CALCDATE";
                    break;
                #endregion
            }
            string conditon = GetPageConditionSql(pageInfo.Condition);
            if (where.Length > 0 && conditon.Length > 0)
                where += " and ";
            if (conditon.Length > 0)
                where += conditon;
            locationReportRep.QueryPage<LocationReportInput>(pageInfo, new SearchInfo( select, tableName, where, orderby));
        }

        public string GetPageConditionSql(LocationReportInput condition)
        {
            var dialect = locationReportRep._Dialect;
            var reportType = condition.ReportTypeEnum;
            SqlCondition[] conditions = null;
            switch (reportType)
            {
                case LocationReportType.待进货单查询报表:
                    conditions = new SqlCondition[]{ 
                        new SqlCondition("P.SheetID"),new SqlCondition("V.vendercode"),new SqlCondition("P.Flag"),
                        new SqlCondition("PI.ORDERDATE1", paramName:"OrderDate",leftNameFormate:"trunc({0})"),new SqlCondition("goodscode", OperateType.LikeBegin),
                        new SqlCondition("goodsname", OperateType.Like), new SqlCondition("G.CELLNO",ingoreValue:"-1"),                       
                    };
                    break;
                case LocationReportType.上架商品信息查询:
                    conditions = new SqlCondition[]{ 
                        new SqlCondition("t4.CELLNO"),new SqlCondition("t4.Locationtype"),new SqlCondition("t4.locationcode", OperateType.LikeBegin),
                        new SqlCondition("t1.objecttype"),new SqlCondition("t3.goodsname", OperateType.Like),
                        new SqlCondition("t3.goodscode", OperateType.LikeBegin),new SqlCondition("t1.Movedate", OperateType.GreatAndEqual,"SelectDateStart"),
                        new SqlCondition("t1.Movedate", OperateType.LessAndEqual,"SelectDateEnd")
                    };
                    break;
                case LocationReportType.下架商品信息查询:
                    conditions = new SqlCondition[]{ 
                    new SqlCondition("t4.CELLNO"),new SqlCondition("t4.Locationtype"),new SqlCondition("t4.locationcode", OperateType.LikeBegin),
                    new SqlCondition("t1.objecttype"),new SqlCondition("offshelvesType"),new SqlCondition("t3.goodsname", OperateType.Like),
                    new SqlCondition("t3.goodscode", OperateType.LikeBegin),new SqlCondition("t1.Movedate", OperateType.GreatAndEqual,"SelectDateStart"),
                    new SqlCondition("t1.Movedate", OperateType.LessAndEqual,"SelectDateEnd"),new SqlCondition("batchno")
                    };
                    break;
                case LocationReportType.货位批次实时库存查询:
                    conditions = new SqlCondition[]{ 
                    new SqlCondition("V.CELLNO"),new SqlCondition("locationcode", OperateType.LikeBegin),new SqlCondition("g.goodscode", OperateType.LikeBegin),
                    new SqlCondition("g.goodsname", OperateType.Like)
                    };
                    break;
                case LocationReportType.货位总量实时库存查询:
                    conditions = new SqlCondition[]{ 
                    new SqlCondition("T.CELLNO"),new SqlCondition("T.locationcode", OperateType.LikeBegin),new SqlCondition("T.goodscode", OperateType.LikeBegin),
                    new SqlCondition("g.goodsname", OperateType.Like)
                    };
                    break;
                case LocationReportType.货位库存日志流水查询:
                    conditions = new SqlCondition[]{ 
                    new SqlCondition("LT.TRANDATE", OperateType.GreatAndEqual,"SelectDateStart"),new SqlCondition("LT.TRANDATE", OperateType.LessAndEqual,"SelectDateEnd"),
                    new SqlCondition("L.CELLNO"),new SqlCondition("L.locationcode", OperateType.LikeBegin),new SqlCondition("LT.SHEETID"),
                    new SqlCondition("G.goodscode", OperateType.LikeBegin),new SqlCondition("g.goodsname", OperateType.Like)
                    };
                    break;
                case LocationReportType.下架指令查询:
                    conditions = new SqlCondition[]{ 
                    new SqlCondition("os.batchno"), new SqlCondition("LM.CELLNO"), new SqlCondition("G.goodscode", OperateType.LikeBegin),
                    new SqlCondition("g.goodsname", OperateType.Like),new SqlCondition("os.flag"),
                    new SqlCondition("os.createdate", OperateType.GreatAndEqual,"SelectDateStart","trunc({0})"),new SqlCondition("os.createdate", OperateType.LessAndEqual,"SelectDateEnd","trunc({0})"),
                    new SqlCondition("os.offshelvesdate", OperateType.GreatAndEqual,"OffshelvesDateStart"),new SqlCondition("os.offshelvesdate", OperateType.LessAndEqual,"OffshelvesDateEnd"),
                    };
                    break;
                case LocationReportType.货位库存调整报表:
                    conditions = new SqlCondition[]{ 
                    new SqlCondition("C.CELLNO"),new SqlCondition("C.locationcode", OperateType.LikeBegin),
                    new SqlCondition("A.createdate", OperateType.GreatAndEqual,"SelectDateStart","trunc({0})"),new SqlCondition("A.createdate", OperateType.LessAndEqual,"SelectDateEnd","trunc({0})"),
                    new SqlCondition("A.goodsbatchcode", OperateType.LikeBegin,"BatchNo"), new SqlCondition("g.goodsname", OperateType.Like),new SqlCondition("A.creator"),                   
                    };
                    break;
                case LocationReportType.货位商品关联查询报表:
                    conditions = new SqlCondition[]{ 
                    new SqlCondition("W.WAREHOUSECODE"),new SqlCondition("L.CELLNO"),new SqlCondition("LG.Status"),
                    new SqlCondition("L.locationcode", OperateType.LikeBegin), new SqlCondition("G.goodscode", OperateType.LikeBegin),new SqlCondition("g.goodsname", OperateType.Like)                   
                    };
                    break;
                case LocationReportType.仓库商品日进销存报表:
                    conditions = new SqlCondition[]{ 
                    new SqlCondition("LID.CALCDATE", OperateType.GreatAndEqual,"SelectDateStart"),new SqlCondition("LID.CALCDATE", OperateType.LessAndEqual,"SelectDateEnd"),
                    new SqlCondition("L.CELLNO"),new SqlCondition("G.goodscode", OperateType.LikeBegin),new SqlCondition("g.goodsname", OperateType.Like),               
                    new SqlCondition("L.locationcode", OperateType.LikeBegin),    
                    };
                    break;
                case LocationReportType.仓库商品汇总进销存报表:
                    conditions = new SqlCondition[]{ 
                    new SqlCondition("LID.calcdate", OperateType.GreatAndEqual,"SelectDateStart"),new SqlCondition("LID.calcdate", OperateType.LessAndEqual,"SelectDateEnd"),
                    new SqlCondition("L.CELLNO"),new SqlCondition("G.goodscode", OperateType.LikeBegin),new SqlCondition("g.goodsname", OperateType.Like),               
                    new SqlCondition("L.locationcode", OperateType.LikeBegin),    
                    };
                    break;
            }
            // StringBuilder sql = new StringBuilder();
            var list = conditions.Select(s => SqlHelper.GetSqlParameter(dialect, s, condition)).Where(s => !string.IsNullOrEmpty(s));
            string sql = string.Join(" and ", list);

            return sql;
        }
    }

    public enum LocationReportType
    {
        /// <summary>
        /// 第一张报表废弃，暂时不用
        /// </summary>
        待进货单查询报表 = 0,
        上架商品信息查询 = 1,
        下架商品信息查询 = 2,
        货位批次实时库存查询 = 3,
        货位总量实时库存查询 = 4,
        货位库存日志流水查询 = 5,
        下架指令查询 = 6,
        货位库存调整报表 = 7,
        货位商品关联查询报表 = 8,
        仓库商品日进销存报表 = 9,
        仓库商品汇总进销存报表 = 10,
    }
}
